﻿using System;
using System.Data;
using System.Data.SqlClient;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using SD.IDAL;
using SD.Entities;

namespace SD.DAL
{
    public class SupportingMaterialDAO : BaseDAO, ISupportingMaterialDAO
    {
        #region ISupportingMaterialDAO Members

        /// <summary>
        /// Get all supporting material
        /// </summary>
        /// <param name="includeInactive">Indicate whether inactive object will be included</param>
        /// <returns>List of supporting material</returns>
        public List<SupportingMaterial> GetAll(bool includeInactive)
        {
            // Obtain a SqlCommand object
            SqlCommand comm = CreateCommand();

            // Assign stored procedure to command object
            comm.CommandText = "SMGetAll";

            // Add parameters to stored procedure
            comm.Parameters.AddWithValue("@includeInactive", includeInactive);

            DataTable dt;

            try
            {
                // execute stored procedure
                dt = ExecuteSelectCommand(comm);
            }
            catch (SqlException ex)
            {
                throw ex;
            }

            // add to result list
            List<SupportingMaterial> lstResult = new List<SupportingMaterial>();
            foreach (DataRow row in dt.Rows)
            {
                SupportingMaterial sm = new SupportingMaterial();
                sm.SupportingMaterialId = row["SupportingMaterialID"].ToString();
                sm.URL = row["URL"].ToString();
                sm.Description = row["Description"].ToString();                
                sm.Type = row["Type"] == DBNull.Value ? null : new ReferenceDataDAO().GetById(row["Type"].ToString());
                sm.AddedBy = new User();
                sm.AddedDate = DateTime.Parse(row["AddedDate"].ToString());
                sm.IsActive = bool.Parse(row["IsActive"].ToString());

                lstResult.Add(sm);
            }


            return lstResult;
        }

        /// <summary>
        /// Get all supporting material of an organization
        /// </summary>
        /// <param name="orgId">ID of the organization</param>
        /// <param name="includeInactive">Indicate whether inactive object will be included</param>
        /// <returns>List of supporting material of an organization</returns>
        public List<SupportingMaterial> GetByOrgId(string orgId, Boolean includeInactive)
        {
            // Obtain a SqlCommand object
            SqlCommand comm = CreateCommand();

            // Assign stored procedure to command object
            comm.CommandText = "SMGetByOrgId";

            // Add parameters to stored procedure
            comm.Parameters.AddWithValue("@includeInactive", includeInactive);
            comm.Parameters.AddWithValue("@OrgID", orgId);

            DataTable dt;

            try
            {
                // execute stored procedure
                dt = ExecuteSelectCommand(comm);
            }
            catch (SqlException ex)
            {
                throw ex;
            }

            // add to result list
            List<SupportingMaterial> lstResult = new List<SupportingMaterial>();
            foreach (DataRow row in dt.Rows)
            {
                SupportingMaterial sm = new SupportingMaterial();
                sm.SupportingMaterialId = row["SupportingMaterialID"].ToString();
                sm.URL = row["URL"].ToString();
                sm.Description = row["Description"].ToString();                
                sm.Type = row["Type"] == DBNull.Value ? null : new ReferenceDataDAO().GetById(row["Type"].ToString());
                sm.AddedBy = new User();
                sm.AddedDate = DateTime.Parse(row["AddedDate"].ToString());
                sm.IsActive = bool.Parse(row["IsActive"].ToString());

                lstResult.Add(sm);
            }

            return lstResult;
        }

        /// <summary>
        /// Get a supporting material by id
        /// </summary>
        /// <param name="smId"></param>
        /// <returns></returns>
        public SupportingMaterial GetById(string smId)
        {
            // Obtain SqlCommand object
            SqlCommand comm = CreateCommand();

            // Assign stored procedure to command object
            comm.CommandText = "SMGetByID";

            // Add parameters to stored procedure
            comm.Parameters.AddWithValue("@SupportingMaterialID", smId);

            DataTable dt;

            try
            {
                // Execute stored procedure
                dt = ExecuteSelectCommand(comm);
            }
            catch (SqlException ex)
            {
                throw ex;
            }

            SupportingMaterial sm = null;

            // if there is one supporting material in table
            if (dt.Rows.Count > 0)
            {
                DataRow row = dt.Rows[0];
                sm = new SupportingMaterial();
                sm.URL = row["URL"].ToString();
                sm.Description = row["Description"].ToString();
                sm.Type = row["Type"] == DBNull.Value ? null : new ReferenceDataDAO().GetById(row["Type"].ToString());
                sm.AddedBy = row["AddedBy"] == DBNull.Value ? null : new UserDAO().GetById(row["AddedBy"].ToString());
                sm.AddedDate = DateTime.Parse(row["AddedDate"].ToString());
                sm.IsActive = bool.Parse(row["IsActive"].ToString());
            }

            return sm;
        }

        /// <summary>
        /// Insert new supporting material to database
        /// </summary>
        /// <param name="sm"></param>
        /// <returns></returns>
        public bool Insert(SupportingMaterial sm)
        {
            int result;

            // Create new command object
            SqlCommand comm = CreateCommand();

            // Assign stored procedure
            comm.CommandText = "SMInsert";

            // Add parameters to commnand
            comm.Parameters.AddWithValue("@URL", sm.URL);
            comm.Parameters.AddWithValue("@Description", sm.Description);
            comm.Parameters.AddWithValue("@Type", sm.Type.RefId);
            comm.Parameters.AddWithValue("@AddedBy", sm.AddedBy.UserId);
            comm.Parameters.AddWithValue("@AddedDate", sm.AddedDate);
            comm.Parameters.AddWithValue("@IsActive", sm.IsActive);
            comm.Parameters.AddWithValue("@OrgID", sm.Organization == null ? null : sm.Organization.OrgId);

            try
            {
                // Execute the stored procedure
                result = ExecuteNonQuery(comm);
            }
            catch (SqlException ex)
            {
                throw ex;
            }

            // Result will be 1 in case of success
            return (result != -1);
        }

        /// <summary>
        /// Update an supporting material
        /// </summary>
        /// <param name="sm">The supporting material will be updated</param>
        /// <returns>True if successful.</returns>
        public bool Update(SupportingMaterial sm)
        {
            int result;

            try
            {
                // Obtain a SqlCommand object
                SqlCommand cmd = CreateCommand();

                //cmd.CommandType = CommandType.Text;
                cmd.CommandText = "SMUpdate";

                // add parameters for the T-SQL command
                cmd.Parameters.AddWithValue("@URL", sm.URL);
                cmd.Parameters.AddWithValue("@Description", sm.Description);
                cmd.Parameters.AddWithValue("@Type", sm.Type.RefId);
                cmd.Parameters.AddWithValue("@SupportingMaterialID", sm.SupportingMaterialId);

                // execute stored procedure
                result = ExecuteNonQuery(cmd);
            }
            catch (SqlException ex)
            {
                throw ex;
            }

            return (result != -1);
        }

        /// <summary>
        /// Delete a supporting material from database
        /// </summary>
        /// <param name="smId"></param>
        /// <returns></returns>
        public bool Delete(string smId)
        {
            throw new NotImplementedException();
        }

        #endregion

        #region ISupportingMaterialDAO Members


        /// <summary>
        /// Select list supporting materials by org id
        /// </summary>
        /// <returns></returns>
        public List<SupportingMaterial> SelectByOrgId(string orgId, int pageNumber, int pageSize, out int totalPages, bool includeInactive)
        {
            // Obtain a SqlCommand object
            SqlCommand comm = CreateCommand();

            // Assign stored procedure to command
            comm.CommandText = "SMSelectByOrgId";

            // Add parameters to stored procedure
            comm.Parameters.AddWithValue("@OrgID", orgId);
            comm.Parameters.AddWithValue("@PageNumber", pageNumber);
            comm.Parameters.AddWithValue("@PageSize", pageSize);
            comm.Parameters.AddWithValue("@IncludeInactive", includeInactive);

            SqlParameter param = comm.CreateParameter();
            param.ParameterName = "@TotalItems";
            param.Direction = ParameterDirection.Output;
            param.DbType = DbType.Int32;
            comm.Parameters.Add(param);

            // execute stored procedure
            DataTable dt = ExecuteSelectCommand(comm);
            
            // calculate how many pages of supporting 
            // materials and set the out parameter
            int totalItems = Int32.Parse(comm.Parameters["@TotalItems"].Value.ToString());

            totalPages = (int)Math.Ceiling((double)totalItems / (double)pageSize);

            // create new list supporting materials 
            List<SupportingMaterial> list = new List<SupportingMaterial>();

            // add each items in table to list
            foreach (DataRow row in dt.Rows)
            {
                SupportingMaterial sm = new SupportingMaterial();
                sm.SupportingMaterialId = row["SupportingMaterialID"].ToString();
                sm.URL = row["URL"].ToString();
                sm.Description = row["Description"].ToString();
                sm.Type = row["Type"] == DBNull.Value ? null : new ReferenceDataDAO().GetById(row["Type"].ToString());
                sm.AddedBy = row["AddedBy"] == DBNull.Value ? null : new UserDAO().GetById(row["AddedBy"].ToString());
                sm.AddedDate = DateTime.Parse(row["AddedDate"].ToString());
                sm.IsActive = bool.Parse(row["IsActive"].ToString());

                list.Add(sm);
            }

            // return list
            return list;

        }

        #endregion

        #region ISupportingMaterialDAO Members


        public bool SetActive(string smId, bool status)
        {
            // Obtain a SqlCommann object
            SqlCommand comm = CreateCommand();

            // Assign stored procedure
            comm.CommandText = "SMUpdateActiveStatus";

            // Add parameters
            comm.Parameters.AddWithValue("@SupportingMaterialID", smId);
            comm.Parameters.AddWithValue("@IsActive", status);

            int result = -1;
            try
            {
                // execute stored procedure
                result = ExecuteNonQuery(comm);
            }
            catch (SqlException ex)
            {
                throw ex;
            }

            // return true if set active successfully, otherwise return false
            return (result != -1);
        }

        #endregion
    }
}
